/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.Deposit_DTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author 10520_000
 */
public class DepositRepository {

    public static int Insert(Deposit_DTO info) {
        int iRet = -1;

        try {
            Connection con = DBManager.getInstance().getConnection();
            float accountbalance = SavingBookRepository.FindByBookID(info.getBookID()).getAccountBalance();
            String sql_deposit = "insert into Deposit values('"
                    + info.getDepositID() + "','"
                    + info.getCustomerName() + "','"
                    + info.getIDNumber() + "','"
                    + info.getBeneficiaryName() + "','"
                    + info.getIDNumberBeneficiary() + "','"
                    + info.getBookID() + "','"
                    + info.getDepositingDate() + "',"
                    + info.getAmountOfMoney() + ")";
            String sql_savingbook = "update SavingBook set AccountBalance=" + (info.getAmountOfMoney() + accountbalance)
                    + ", SBStatus='true'"
                    + " where BookID='" + info.getBookID() + "'";
            con.createStatement().executeUpdate(sql_deposit);
            con.createStatement().executeUpdate(sql_savingbook);

            iRet = 1;
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    //
    public static int Update(Deposit_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "UPDATE DEPOSIT SET DepositID =?, DepositingDate =?, CustomerName= ?, BeneficiaryName= ?, BookID= ?, AmountOfMoney= ? WHERE DepositID=?";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.setString(1, info.getDepositID());
            pstmt.setDate(2, (Date) info.getDepositingDate());
            pstmt.setString(3, info.getCustomerName());
            pstmt.setString(4, info.getBeneficiaryName());
            pstmt.setString(5, info.getBookID());
            pstmt.setInt(6, info.getAmountOfMoney());
            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    public static int Delete(Deposit_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE DEPOSIT WHERE CustomerID=?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getDepositID());

            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String SQL = "DELETE FROM DEPOSIT";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM DEPOSIT ORDER BY DepositID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Deposit_DTO info = new Deposit_DTO();
                info.setDepositID(rs.getString("DepositID"));
                info.setBookID(rs.getString("BookID"));
                info.setAmountOfMoney(rs.getInt("AmountOfMoney"));
                info.setBeneficiaryName(rs.getString("BeneficiaryName"));
                info.setCustomerName(rs.getString("CustomerName"));
                info.setDepositingDate(rs.getDate("DepositingDate"));

                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static ArrayList FindByDepositID(String DepositID) {
        ArrayList arr = new ArrayList();

        try {
            String QRY = "SELECT * FROM DEPOSIT WHERE DepositID LIKE(?) ORDER BY DepositID";
            Connection con = DBManager.getInstance().getConnection();
            PreparedStatement pstmt = con.prepareStatement(QRY);
            pstmt.setString(1, "%" + DepositID + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Deposit_DTO info = new Deposit_DTO();
                info.setDepositID(rs.getString("DepositID"));
                info.setBookID(rs.getString("BookID"));
                info.setAmountOfMoney(rs.getInt("AmountOfMoney"));
                info.setBeneficiaryName(rs.getString("BeneficiaryName"));
                info.setCustomerName(rs.getString("CustomerName"));
                info.setDepositingDate(rs.getDate("DepositingDate"));

                arr.add(info);
            }

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static void main(String[] str) {
        Deposit_DTO info = new Deposit_DTO();
        info.setDepositID("");
        info.setCustomerName("DO NGOC SAM");
        info.setIDNumber("215250401");
        info.setBeneficiaryName("DO NGOC SAM");
        info.setIDNumberBeneficiary("215250401");
        info.setBookID("MS00002");
        info.setDepositingDate(new Date(2012, 3, 3));
        info.setAmountOfMoney(300);
        Insert(info);

    }
}
